<?php

/* ========================================================================= */
/* This file is part of the HypnoPHP Framework Library                       */
/* Copyright (c) 2009-2010, Radu Buzea                                       */
/* ========================================================================= */

/**
 @author Radu Buzea
 @copyright Copyright (c) 2009-2010, Radu Buzea (www.hypnosis.ro)
 @license GNU Lesser General Public License (www.gnu.org/copyleft/lesser.html)
 @package MySQL Module for HypnoPHP
*/

// ============================================================================
// Class: Module_MySQL_Service
// ============================================================================

class Module_MySQL_Service extends HBaseDb
{

	protected $__last_query = null;
	protected $__table_fields = null;

	const MYSQL_ERROR_MESSAGE = '%s (Error code #%d)';
	const QUERY_FUNCTIONS_REGEXP = '/^(AVG|COUNT|FIRST|LAST|SUM|MAX|MIN)[\(](.*)[\)]$/i';

	# ###########################################################################

	/**
	* Connect to the database server and select the database
	*
	* @return bool
	*/
	public function connect()
	{
		if ($this->db_keep_alive && $this->isReady()) { return true; }
		if (empty($this->db_host)) { $this->db_host = 'localhost'; }
		if (empty($this->db_name)) {
			$this->addError("The name of the MySQL database was not specified", E_USER_NOTICE);
			return false;
		}
		if ($this->db_link_id == 0) {
			$host_port = trim($this->db_host);
			if (isset($this->db_port) && trim($this->db_port) != '') {
				$host_port .= ':' . trim($this->db_port);
			}
			$this->db_link_id = @mysql_connect($host_port, $this->db_user, $this->db_pass);
			if (mysql_errno() != 0) {
				$this->addError(sprintf(self::MYSQL_ERROR_MESSAGE, mysql_error(), mysql_errno()),
					E_USER_WARNING);
				return false;
			}
		}
		if ($this->db_link_id > 0) {
			$this->db_selected = @mysql_select_db($this->db_name, $this->db_link_id);
			if (mysql_errno($this->db_link_id) != 0) {
				$this->addError(sprintf(self::MYSQL_ERROR_MESSAGE, mysql_error($this->db_link_id),
					mysql_errno($this->db_link_id)), E_USER_WARNING);
				$this->disconnect();
				return false;
			}
		}
		if (!$this->isReady()) {
			$this->addError("Cannot connect to the MySQL database server <i>" .
				$this->db_host . "</i>", E_USER_WARNING);
			return false;
		}
		return true;
	}

	# ###########################################################################

	/**
	* Disconnect from the database server
	*
	* @return bool
	*/
	public function disconnect()
	{
		if ($this->db_link_id == 0) { return false; }
		mysql_close($this->db_link_id);
		$this->db_link_id = 0;
		$this->db_connected = false;
		return true;
	}

	# ###########################################################################

	/**
	* Get the last error code reported by the database server
	*
	* @return integer
	*/
	public function errorNum()
	{
		if (!$this->isConnected()) { return 0; }
		return mysql_errno($this->db_link_id);
	}

	# ###########################################################################

	/**
	* Get the last error message reported by the database server
	*
	* @return string
	*/
	public function errorTxt()
	{
		if (!$this->isConnected()) { return ''; }
		return mysql_error($this->db_link_id);
	}

	# ###########################################################################

	/**
	* Execute an SQL query
	*
	* @param miexed $sql (string or Module_MySQL_Query object)
	* @return resource or false on errors
	*/
	public function query($sql)
	{
		if (is_object($sql) && is_subclass_of($sql, 'HBaseDbQuery')) {
			$sql = $sql->execute();
		}
		if (!$this->isConnected() || !is_string($sql) || trim($sql) == '') { return null; }
		$this->last_sql = $sql;
		$this->__last_query = mysql_query($sql, $this->db_link_id);
		return $this->__last_query;
	}

	# ###########################################################################

	/**
	* Get information about the most recent query
	*
	* @return string or FALSE on failure
	*/
	public function query_info()
	{
		if ($this->db_link_id == 0) { return false; }
		return mysql_info($this->db_link_id);
	}

	# ###########################################################################

	/**
	* Fetch a result row as an associative array, a numeric array, or both
	*
	* @param resource $db_result The result resource that is being evaluated
	* @param integer $array_type The type of array that is to be fetched
	* (MYSQL_BOTH, MYSQL_ASSOC, MYSQL_NUM)
	* @return array or null
	*/
	public function fetchArray($db_result, $array_type = MYSQL_BOTH)
	{
		if (is_resource($db_result)) { return @mysql_fetchArray($db_result, $array_type); }
		return null;
	}

	# ###########################################################################

	/**
	* Get a result row as an enumerated array
	*
	* @param resource $db_result The result resource that is being evaluated
	* @return array or null
	*/
	public function fetchRow($db_result)
	{
		if (is_resource($db_result)) { return @mysql_fetchRow($db_result); }
		return null;
	}

	# ###########################################################################

	/**
	* Fetch a result row as an associative array
	*
	* @param resource $db_result The result resource that is being evaluated
	* @return array or null
	*/
	public function fetchAssoc($db_result)
	{
		if (is_resource($db_result)) { return @mysql_fetchAssoc($db_result); }
		return null;
	}

	# ###########################################################################

	/**
	* Fetch a result row as an object
	*
	* @param resource $db_result The result resource that is being evaluated
	* @return object or null
	*/
	public function fetchObject($db_result)
	{
		if (is_resource($db_result)) { return @mysql_fetchObject($db_result); }
		return null;
	}

	# ###########################################################################

	/**
	* Get column information from a result and return as an object
	*
	* @param resource $db_result The result resource that is being evaluated
	* @param integer index The numerical field offset
	* @return object (Module_MySQL_Field) or null
	*/
	public function fetchField($db_result, $index = 0)
	{
		if (is_resource($db_result) && $index >= 0) {
			$field = @mysql_fetchField($db_result, $index);
			return new Module_MySQL_Field($field);
		}
		return null;
	}

	# ###########################################################################

	/**
	* Returns the length of the specified field
	*
	* @param resource $db_result The result resource that is being evaluated
	* @param integer index The numerical field offset
	* @return integer (0 'zero' on error)
	*/
	public function fieldLength($db_result, $index = 0)
	{
		if (is_resource($db_result) && $index >= 0) {
			return @mysql_field_len($db_result, $index);
		}
		return 0;
	}

	# ###########################################################################

	/**
	* Get the flags associated with the specified field in a result
	*
	* @param resource $db_result The result resource that is being evaluated
	* @param integer index The numerical field offset
	* @return string or null
	*/
	public function fieldFlags($result, $index = 0)
	{
		if (is_resource($db_result) && $index >= 0) {
			return @mysql_fieldFlags($db_result, $index);
		}
		return null;
	}

	# ###########################################################################

	/**
	* Free database result from memory
	*
	* @param resource $db_result The result resource that must be flushed
	* @return bool
	*/
	public function freeResult($db_result)
	{
		if (is_resource($db_result)) {
			return @mysql_freeResult($db_result);
		}
		return false;
	}

	# ###########################################################################

	/**
	* Get the ID generated from the previous INSERT operation
	*
	* @return integer or null
	*/
	public function insertId()
	{
		if ($this->isConnected()) {
			return @mysql_insertId($this->db_link_id);
		}
		return null;
	}

	# ###########################################################################

	/**
	* List the fileds from a database table
	*
	* @param string $table_name
	* @param string $schema (optional)
	* @return array (list of fields) or bool (false on error)
	*/
	public function listFields($table_name, $schema = '')
	{
		if (!is_string($table_name) || trim($table_name) == '') {
			return false;
		}
		$table_name = trim($table_name);
		$key = $table_name;
		if (is_string($schema) && trim($schema) != '') {
			$key = $schema . '.' . $table_name;
		}
		if (!isset($this->__table_fields) || !is_array($this->__table_fields)) {
			$this->__table_fields = array();
		} else if (isset($this->__table_fields[$key]) && is_array($this->__table_fields[$key])) {
			return $this->__table_fields[$key];
		}
		$db_result = mysql_query("SHOW COLUMNS FROM `$table_name`");
		if ($db_result) {
			$fields = array();
			if (mysql_numRows($db_result) > 0) {
				while ($raw_field_data = mysql_fetchAssoc($db_result)) {
					$field_data = array();
					foreach ($raw_field_data as $field_key => $field_value) {
						$field_key = strtolower($field_key);
						if ($field_key == 'field') { $field_key = 'name'; }
						$field_data[$field_key] = $field_value;
					}
					if (isset($field_data['name'])) {
						$fields[$field_data['name']] = $field_data;
					}
				}
			}
			if (count($fields) > 0) {
				$this->__table_fields[$key] = $fields;
			}
		}
		if (isset($this->__table_fields[$key])) {
			return $this->__table_fields[$key];
		}
		return false;
	}

	# ###########################################################################

	/**
	* Get number of rows in result
	*
	* @param resource $db_result The result resource that is being evaluated
	* @return integer or null
	*/
	public function numRows($db_result)
	{
		if (is_resource($db_result)) { return @mysql_numRows($db_result); }
		return null;
	}

	# ###########################################################################

	/**
	* Sets the database character set
	*
	* @param string $charset The character set name
	* @return bool
	*/
	public function setCharset($charset)
	{
		if ($this->isReady() && is_string($charset)) {
			return @mysql_setCharset($charset, $this->db_link_id);
		}
		return false;
	}

	# ###########################################################################

	/**
	* Escapes special characters in a string and quote the string for use
	* in a SQL statement
	*
	* @param mixed $entity Data that is to be escaped
	* @param string $type The data 'type hinting'
	* @return string
	*/
	public function smartQuote($entity, $type = '')
	{
		if (is_object($entity)) {
			$this->addError("Invalid entity type", E_USER_WARNING);
			return '';
		}
		$type = trim(strtoupper($type));
		$types = $this->enumTypes();
		if (isset($types[$type])) {
			switch ($types[$type][0]) {
				case 'integer':
					if (is_int($entity)) { return $entity; }
					return intval($entity);
				case 'float':
					if (is_int($entity)) { return $entity; }
					elseif (!is_float($entity)) { $entity = floatval($entity); }
					return sprintf('%F', $entity);
				case 'string-array':
					if (is_string($entity)) {
						if (strstr($entity, ",")) {
							$entities = explode(",", $entity);
						} else {
							$entities = array($entity);
						}
					} elseif (is_array($entity)) {
						$entitys = $entity;
					} else {
						$entities = array(strval($entity));
					}
					$result = '';
					foreach ($entities as $key => $entity) {
						if ($key > 0) { $result .= ","; }
						$result .= "'" . mysql_real_escape_string($entity) . "'";
					}
					return $result;
				default:
					return "'" . mysql_real_escape_string($entity) . "'";
			}
		}
		if (strstr($type, "_")) { $type = str_replace("_", "", $type); }
		switch ($type) {
			case 'FIELD':
			case 'FIELDNAME':
			case 'SCHEMA':
			case 'SCHEMANAME':
			case 'TABLE':
			case 'TABLENAME':
				return "`" . mysql_real_escape_string($entity) . "`";
			default:
				return "'" . mysql_real_escape_string($entity) . "'";
		}
		return $entity;
	}

	# ###########################################################################

	/**
	* Escapes special characters in a string for use in a SQL statement
	*
	* @param mixed $str Data that is to be escaped
	* @return string
	*/
	public function escapeStr($str)
	{
		if (is_string($str) && $str != '') { return mysql_escape_string($str); }
		elseif (is_int($str) || is_float($str)) { return $str; }
		elseif (is_bool($str)) { return ($str) ? 1 : 0; }
		return '';
	}

	# ###########################################################################

	/**
	* Escapes special characters in a string for use as a table name or
	* as a schema name in a SQL statement
	*
	* @param string $str The string (name) that is to be escaped
	* @return string
	*/
	public function escape_name_part($str = '')
	{
		$str = trim($str);
		if ($str == "*") { return $str; }
		if (preg_match_all(self::QUERY_FUNCTIONS_REGEXP, $str, $matches)) {
			if (isset($matches[1][0]) && isset($matches[2][0])) {
				return $matches[1][0]. "(" . $this->escapeNames($matches[2][0]) . ")";
			}
		}
		if (strstr($str, '`')) { $str = str_replace("`", "", $str); }
		$p = strpos(strtoupper($str), "DISTINCT");
		if ($p !== false && $p == 0) {
			$str = substr($str, 8);
			return 'DISTINCT `' . $this->escapeStr(trim($str)) . '`';
		}
		return '`' . $this->escapeStr($str) . '`';
	}

	# ###########################################################################

	/**
	* Escapes special characters in a complex string for use as a table name and/or
	* as a schema name in a SQL statement
	*
	* @param string $str The string (name) that is to be escaped
	* @return string
	*/
	public function escapeNames($str)
	{
		if (!is_string($str)) { return $str; }
		$str = trim($str);
		if (strstr($str, '.')) {
			if (preg_match_all(self::QUERY_FUNCTIONS_REGEXP, $str, $matches)) {
				return $this->escape_name_part($str);
			}
			$parts = explode('.', $str);
			$str = '';
			foreach ($parts as $key => $part) {
				if ($key > 0) { $str .= '.'; }
				$str .= $this->escape_name_part($part);
			}
			return $str;
		}
		return $this->escape_name_part($str);
	}

	# ###########################################################################

	/**
	* Enumerate MySQL type definitions
	*
	* @return array (internal type definition)
	*/
	public function enumTypes()
	{
		$result = array(
			'CHAR' => array('string', 'CHAR(%d)'),
			'VARCHAR' => array('string', 'VARCHAR(%d)'),
			'TINYTEXT' => array('string', 'TINYTEXT'),
			'TEXT' => array('string', 'TEXT'),
			'BLOB' => array('binary', 'BLOB'),
			'MEDIUMTEXT' => array('string', 'MEDIUMTEXT'),
			'MEDIUMBLOB' => array('binary', 'MEDIUMBLOB'),
			'LONGTEXT' => array('string', 'LONGTEXT'),
			'LONGBLOB' => array('binary', 'LONGBLOB'),
			'ENUM' => array('string-array', 'ENUM(%s)'),
			'SET' => array('string-array', 'SET(%s)'),
			'TINYINT' => array('integer', 'TINYINT(%d)'),
			'SMALLINT' => array('integer', 'SMALLINT(%d)'),
			'MEDIUMINT' => array('integer', 'MEDIUMINT(%d)'),
			'INT' => array('integer', 'INT(%d)'),
			'BIGINT' => array('integer', 'BIGINT(%d)'),
			'FLOAT' => array('float', 'FLOAT(%d,%d)'),
			'DOUBLE' => array('float', 'DOUBLE(%d,%d)'),
			'DECIMAL' => array('float', 'DECIMAL(%d,%d)'),
			'DATE' => array('string', 'DATE'),
			'DATETIME' => array('string', 'DATETIME'),
			'TIMESTAMP' => array('string', 'TIMESTAMP'),
			'DATE' => array('string', 'DATE'),
			'TIME' => array('string', 'TIME'),
			'YEAR' => array('integer', 'YEAR'),
		);
	}

	# ###########################################################################

	/**
	* Check MySQL type name
	*
	* @param string $type
	* @param bool $return_type_info
	* @return array (if $return_type_info is true) or
	* bool (if $return_type_info is false or if the type is not a valid MySQL type)
	*/
	public function checkType($type = '', $return_type_info = true)
	{
		$type = trim(strtoupper($type));
		$types = $this->enumTypes();
		if ($return_type_info && isset($types[$type])) { return $types[$type]; }
		return isset($types[$type]);
	}

	# ###########################################################################

	/**
	* Enumerate tables from the selected database
	*
	* @return array or null on error
	*/
	public function tablesList()
	{
		if (!$this->isReady()) { return null; }
		$db_result = $this->query("SHOW TABLE STATUS FROM `" . $this->escapeStr(trim($this->db_name)) . "`");
		$result = array();
		if ($db_result) {
			while ($result_row = $this->fetchAssoc($db_result)) {
				$result[$result_row['Name']] = $result_row;
			}
		}
		return $result;
	}

	# ###########################################################################

}
